﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyThuVien.DTO;
using System.Data;
using System.Windows.Forms;

namespace QuanLyThuVien.DAO
{
    class SachDAO
    {
        public static void SuaSoLuong(SachDTO sa)
        {
            NorthwindDataContext QLTV = new NorthwindDataContext();
            Sach s = QLTV.Saches.Single(i => i.MaSach == sa.Masach1);
            s.SoSachMuon = sa.SoSachMuon1;
            QLTV.SubmitChanges();
        }

        public static Sach ThongTin_Sach(int maSach)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var sach = db.Saches.Single(s => s.MaSach == maSach);
            return sach;
        }

        public static DataTable XuLyMuon_Sach(int MaSach)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sach = from s in cnn.Saches
                       where s.MaSach == MaSach
                       select new 
                       { 
                           s.MaSach,
                           s.SoLuong, 
                           s.SoSachMuon,                            
                       };
            DataTable dt = new DataTable();
            dt.Columns.Add("MaSach",typeof(int));
            dt.Columns.Add("SoLuong",typeof(int));
            dt.Columns.Add("SoSachMuon",typeof(int));
            foreach(var gan in sach)
            {
                dt.Rows.Add(gan.MaSach,gan.SoLuong,gan.SoSachMuon);
            }
            return dt;
        }

        
        public static DataTable laydanhsachsach(int masach)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = from s in cnn.Saches
                       join t in cnn.TheLoais on s.MaTheLoai equals t.MaTheLoai
                       join g in cnn.TacGias on s.MaTacGia equals g.MaTacGia
                       where s.MaSach == masach
                       select new { s.TenSach, t.TenTheLoai, g.TenTacGia, s.SoLuong, s.SoSachMuon };
            DataTable dt = new DataTable();
            dt.Columns.Add("TenSach",typeof(string));
            dt.Columns.Add("TenTheLoai", typeof(string));
            dt.Columns.Add("TentacGia", typeof(string));
            dt.Columns.Add("SoLuong", typeof(int));
            dt.Columns.Add("SoSachMuon", typeof(int));
            foreach (var gan in sql)
            {
                dt.Rows.Add(gan.TenSach, gan.TenTheLoai, gan.TenTacGia, gan.SoLuong, gan.SoSachMuon);
            }
            return dt;
        }
    

        //trung
        public static DataGridView GetAllS()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            DataGridView dt = new DataGridView();
            var sql = from S in db.Saches
                      join TL in db.TheLoais on S.MaTheLoai equals TL.MaTheLoai
                      join TG in db.TacGias on S.MaTacGia equals TG.MaTacGia
                      join NV in db.NhanViens on S.MaNhanVien equals NV.MaNhanVien
                      join NSX in db.NhaSanXuats on S.MaNhaSanXuat equals NSX.MaNhaSanXuat
                      select new
                      {
                          S.MaSach,
                          S.TenSach,
                          S.NamXuatBan,
                          S.NgayNhap,
                          S.TriGia,
                          S.MaNhanVien,
                          S.MaTheLoai,
                          S.MaTacGia,
                          S.MaNhaSanXuat,
                          S.SoLuong,
                          S.SoSachMuon,
                          NV.HoTenNhanVien,
                          NSX.TenNhaSanXuat,
                          TL.TenTheLoai,
                          TG.TenTacGia,
                      };
            dt.DataSource = sql;
            return dt;
        }


        public static DataGridView GetSoLuong(int MaSach)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            DataGridView dt = new DataGridView();
            var sql = from S in db.Saches where S.MaSach == MaSach select new { S.SoLuong };
            dt.DataSource = sql;
            return dt;
        }

        public static void Them(Sach S)
        {
            NorthwindDataContext context = new NorthwindDataContext();
            Sach a = new Sach();
            a.TenSach = S.TenSach;
            a.MaTacGia = S.MaTacGia;
            a.NamXuatBan = S.NamXuatBan;
            a.NgayNhap = S.NgayNhap;
            a.MaTheLoai = S.MaTheLoai;
            a.TriGia = S.TriGia;
            a.MaNhanVien = S.MaNhanVien;
            a.MaNhaSanXuat = S.MaNhaSanXuat;
            a.MaTacGia = S.MaTacGia;
            a.SoLuong = S.SoLuong;
            a.SoSachMuon = 0;

            context.Saches.InsertOnSubmit(a);
            context.SubmitChanges();
        }

        public static void Xoa(Sach S)
        {
            NorthwindDataContext context = new NorthwindDataContext();
            Sach a = context.Saches.Single(ssach => ssach.MaSach == S.MaSach);
            context.Saches.DeleteOnSubmit(a);
            context.SubmitChanges();

        }

        public static void GiamSoLuong(int MaSach, int SoLuong)
        {
            NorthwindDataContext context = new NorthwindDataContext();
            Sach a = context.Saches.Single(ssach => ssach.MaSach == MaSach);
            a.SoLuong = a.SoLuong - SoLuong;
            context.SubmitChanges();

        }

        public static void TangSoLuong(int MaSach, int SoLuong)
        {
            NorthwindDataContext context = new NorthwindDataContext();
            Sach a = context.Saches.Single(ssach => ssach.MaSach == MaSach);
            a.SoLuong = a.SoLuong + SoLuong;
            context.SubmitChanges();

        }

        public static void Sua(Sach S)
        {

            NorthwindDataContext context = new NorthwindDataContext();
            Sach a = context.Saches.Single(ssach => ssach.MaSach == S.MaSach);
            a.TenSach = S.TenSach;
            a.MaTacGia = S.MaTacGia;
            a.NamXuatBan = S.NamXuatBan;
            a.NgayNhap = S.NgayNhap;
            a.MaTheLoai = S.MaTheLoai;
            a.TriGia = S.TriGia;
            a.MaNhanVien = S.MaNhanVien;
            a.MaNhaSanXuat = S.MaNhaSanXuat;
            a.SoLuong = S.SoLuong;
            context.SubmitChanges();
        }


        public static DataGridView GetMaSach(string TenSach)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            DataGridView dt = new DataGridView();
            var sql = from S in db.Saches where S.TenSach == TenSach select S;
            dt.DataSource = sql;
            return dt;
        }

        public static DataGridView GetMaSachInCTPM(string TenSach)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            DataGridView dt = new DataGridView();
            var sql = from S in db.Saches
                      join CTPM in db.ChiTietPhieuMuons on S.MaSach equals CTPM.MaSach
                      join GNMS in db.PhieuGhiNhanMatSaches on CTPM.MaChiTietPhieuMuon equals GNMS.MaChiTietPhieuMuon
                      join NV in db.NhanViens on GNMS.MaNhanVien equals NV.MaNhanVien
                      where S.TenSach == TenSach
                      select new
                      {
                          S.MaSach,
                          S.TenSach,
                          CTPM.MaChiTietPhieuMuon,
                          GNMS.MaPhieuGhiNhanMatSach,
                          GNMS.NgayGhiNHan,
                          GNMS.MaNhanVien,
                          NV.HoTenNhanVien,

                      };
            dt.DataSource = sql;
            return dt;
        }

     //phat huy

        public static DataTable Tim_Kiem_Sach(string tensach, string tacgia, string theloai, string nhasx)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var Query = from i in db.Saches
                        join tg in db.TacGias on i.MaTacGia equals tg.MaTacGia
                        join tl in db.TheLoais on i.MaTheLoai equals tl.MaTheLoai
                        join nsx in db.NhaSanXuats on i.MaNhaSanXuat equals nsx.MaNhaSanXuat
                        where i.TenSach.Contains(tensach) && tg.TenTacGia.Contains(tacgia)
                                            && tl.TenTheLoai.Contains(theloai) && nsx.TenNhaSanXuat.Contains(nhasx)
                        orderby i.TenSach, tg.TenTacGia
                        select new
                        {
                            i.MaSach,
                            i.TenSach,
                            i.NamXuatBan,
                            nsx.TenNhaSanXuat,
                            tl.TenTheLoai,
                            tg.TenTacGia,
                            i.SoLuong,
                            i.SoSachMuon,
              
                        };

            DataTable dt = new DataTable();

            dt.Columns.Add("Tên Sách", typeof(string));
            dt.Columns.Add("Tác Giả", typeof(string));
            dt.Columns.Add("Thể Loại", typeof(string));
            dt.Columns.Add("Năm Xuất Bản", typeof(int));
            dt.Columns.Add("Nhà Xuất Bản", typeof(string));
            dt.Columns.Add("Tình Trạng", typeof(string));

            foreach (var gtri in Query)
            {
                if (gtri.SoLuong - gtri.SoSachMuon > 0)
                {
                    dt.Rows.Add(gtri.TenSach, gtri.TenTacGia, gtri.TenTheLoai, gtri.NamXuatBan, gtri.TenNhaSanXuat, "SÁCH CÒN TRONG KHO.");
                }
                else
                {
                    dt.Rows.Add(gtri.TenSach, gtri.TenTacGia, gtri.TenTheLoai, gtri.NamXuatBan, gtri.TenNhaSanXuat, "SÁCH HIỆN THỜI KHÔNG CÒN TRONG KHO.");
                }
            }
            return dt;
        }

        //kiem tra sach co hay chua
        public static int KT_Sach(string tenSach, string tenTG)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var ds = from s in db.Saches
                     join tg in db.TacGias on s.MaTacGia equals tg.MaTacGia
                     where s.TenSach == tenSach && tg.TenTacGia == tenTG
                     select s;
            if (ds.Count() == 0)
            {
                return 0;
            }
            return 1;

        }
    
    }
}
